<?php

App::uses('Crud', 'Model');
App::uses('Certification', 'Model');
App::uses('CertificationDetail', 'Model');
App::uses('Workstation', 'Model');
App::uses('WorkstationConfig', 'Model');
App::uses('Line', 'Model');
App::uses('Area', 'Model');
App::uses('Moe', 'Model');
App::uses('User', 'Model');
App::uses('Crud', 'Model');

class UserArea extends Crud
{

    const STATUS_DISABLED = 0;
    const STATUS_ENABLED = 1;
    public $useTable = 'user_areas';

    /**
     * Obtenemos todos registros habilitados.
     * @return array
     */
    public function getByUser($userId)
    {
        $filters = array(
            'fields' => array(
                'id',
                'area_id',
                'a.name as area',
                'm.name as moe',
            ),
            'joins' => array(
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'INNER',
                    'conditions' => array(
                        'UserArea.area_id = a.id',
                    ),
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'INNER',
                    'conditions' => array(
                        'a.moe_id = m.id',
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.user_id' => $userId,
                'UserArea.status' => self::STATUS_ENABLED
            ),
            'order' => array(
                'm.name' => 'ASC',
                'a.name' => 'ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getCertificationsInTraining($userId)
    {
        $filters = array(
            'fields' => array(
                'c.id',
                'c.end',
                'u.full_name as user',
                'a.name as area',
                'l.name as line',
                'w.description as workstation',
            ),
            'joins' => array(
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'INNER',
                    'conditions' => array(
                        'a.status' => Area::STATUS_ENABLED,
                        'a.id = UserArea.area_id',
                    ),
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'INNER',
                    'conditions' => array(
                        'm.status' => Moe::STATUS_ENABLED,
                        'm.id = a.moe_id',
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'INNER',
                    'conditions' => array(
                        'l.status' => Line::STATUS_ENABLED,
                        'l.area_id = a.id',
                    ),
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.status' => Workstation::STATUS_ENABLED,
                        'w.line_id = l.id',
                    ),
                ),
                array(
                    'table' => 'certifications',
                    'alias' => 'c',
                    'type' => 'INNER',
                    'conditions' => array(
                        'c.status' => Certification::STATUS_ENABLED,
                        'c.workstation_id = w.id',
                        'c.training' => 1,
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'INNER',
                    'conditions' => array(
                        'u.status' => User::STATUS_ENABLED,
                        'c.user_id = u.id',
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.status' => self::STATUS_ENABLED,
                'UserArea.user_id' => $userId,
                'DATE_SUB(c.end, INTERVAL 7 DAY) <= now()'
            ),
            'order' => array(
                'c.end ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getCertificationsSuccess($userId)
    {
        $filters = array(
            'fields' => array(
                'c.id',
                'c.life',
                'u.full_name as user',
                'a.name as area',
                'l.name as line',
                'w.description as workstation',
            ),
            'joins' => array(
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'INNER',
                    'conditions' => array(
                        'a.status' => Area::STATUS_ENABLED,
                        'a.id = UserArea.area_id',
                    ),
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'INNER',
                    'conditions' => array(
                        'm.status' => Moe::STATUS_ENABLED,
                        'm.id = a.moe_id',
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'INNER',
                    'conditions' => array(
                        'l.status' => Line::STATUS_ENABLED,
                        'l.area_id = a.id',
                    ),
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.status' => Workstation::STATUS_ENABLED,
                        'w.line_id = l.id',
                    ),
                ),
                array(
                    'table' => 'certifications',
                    'alias' => 'c',
                    'type' => 'INNER',
                    'conditions' => array(
                        'c.status' => Certification::STATUS_ENABLED,
                        'c.workstation_id = w.id',
                        'c.success' => 1,
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'INNER',
                    'conditions' => array(
                        'u.status' => User::STATUS_ENABLED,
                        'c.user_id = u.id',
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.status' => self::STATUS_ENABLED,
                'UserArea.user_id' => $userId,
                'DATE_SUB(c.life, INTERVAL 90 DAY) <= now()'
            ),
            'order' => array(
                'c.life ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    /**
     * @param $userId
     * @param $startDate
     * @return array
     */
    public function getUsersForShiftModel($userId, $startDate)
    {
        $status = array(
            User::STATUS_ENABLED,
            User::STATUS_INCAPACIDAD,
            User::STATUS_VACACIONES,
        );
        $categories = array(
            Category::ASOCIADO,
        );
        $filters = array(
            'fields' => array(
                'sm.id',
                'sm.work_date as wd',
                'u.id as u',
                'u.nomina as nom',
                's.code as s',
                's.name as sn',
                'a.number as a',
                'a.name as an',
                'a.code as ac',
                'a.colour as aco',
                'l.number as l',
                'l.name as ln',
                'l.colour as lc',
            ),
            'joins' => array(
                array(
                    'table' => 'user_areas',
                    'alias' => 'ua',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserArea.area_id = ua.area_id',
                        'ua.status' => self::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserArea.user_id = u.id',
                        'u.category_id' => $categories,
                        'u.status' => $status,
                    ),
                ),
                array(
                    'table' => 'shift_models',
                    'alias' => 'sm',
                    'type' => 'left',
                    'conditions' => array(
                        'sm.user_id = u.id',
                        'sm.work_date =' => $startDate,
                    ),
                ),
                array(
                    'table' => 'shifts',
                    'alias' => 's',
                    'type' => 'left',
                    'conditions' => array(
                        'sm.shift_code = s.code',
                    ),
                ),
                array(
                    'table' => 'assists',
                    'alias' => 'a',
                    'type' => 'left',
                    'conditions' => array(
                        'sm.assist_number = a.number',
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'left',
                    'conditions' => array(
                        'l.id = sm.line_id',
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.user_id' => $userId,
                'UserArea.status' => self::STATUS_ENABLED,
            ),
            'order' => array(
                'u.nomina ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getLinesByUser($userId)
    {
        $filters = array(
            'fields' => array(
                'l.number as value',
                'l.name as text',
            ),
            'joins' => array(
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserArea.area_id = l.area_id',
                        'l.status' => Line::STATUS_ENABLED,
                        'l.type' => Line::TYPE_REAL,
                    ),
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserArea.area_id = a.id',
                        'a.status' => Area::STATUS_ENABLED,
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.user_id' => $userId,
                'UserArea.status' => self::STATUS_ENABLED,
            ),
            'order' => array(
                'a.name ASC',
                'l.name ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    /**
     * Obtenemos los usuarios que tienen diferente nivel antes de la migracion y despues de la migracion
     * @userId Buscamos las areas que tiene asignadas este usuario
     */
    public function getAsociates($userId)
    {
        $filters = array(
            'fields' => array(
                'u.nomina',
                'u.full_name as name',
                'u.curp',
                'UserLine.certifications',
                'UserLine.trainings',
                'UserLine.assign_date',
                'UserLine.days',
                'l.name as line',
                'a.name as area',
                'm.name as moe',
                'c.name as level_name',
                'c.icon as level_icon',
                'c.colour as level_colour',
                'c2.name as level_migration_name',
                'c2.icon as level_migration_icon',
                'c2.colour as level_migration_colour',
                'c3.name as level_excel_name',
                'c3.icon as level_excel_icon',
                'c3.colour as level_excel_colour',
            ),
            'joins' => array(
                array(
                    'table' => 'areas',
                    'alias' => '_a',
                    'type' => 'inner',
                    'conditions' => array(
                        '_a.status' => Area::STATUS_ENABLED,
                        'UserArea.area_id = _a.id',
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => '_l',
                    'type' => 'inner',
                    'conditions' => array(
                        '_l.status' => Line::STATUS_ENABLED,
                        '_l.area_id = _a.id',
                        '_l.type' => Line::TYPE_VIRTUAL,
                    ),
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'UserLine',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserLine.status' => UserLine::STATUS_ENABLED,
                        'UserLine.line_id = _l.id',
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'inner',
                    'conditions' => array(
                        'u.id = UserLine.user_id',
                        'u.status' => array(User::STATUS_ENABLED, User::STATUS_INCAPACIDAD, User::STATUS_VACACIONES),
                        'u.category_id' => array(Category::ASOCIADO),
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserLine.line_id = l.id',
                        'l.status' => Line::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.area_id = a.id',
                        'a.status' => Area::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.moe_id = m.id',
                        'm.status' => Moe::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'categorizations',
                    'alias' => 'c',
                    'type' => 'inner',
                    'conditions' => array(
                        'c.id = UserLine.level',
                    ),
                ),
                array(
                    'table' => 'categorizations',
                    'alias' => 'c2',
                    'type' => 'inner',
                    'conditions' => array(
                        'c2.id = UserLine.level_migration',
                    ),
                ),
                array(
                    'table' => 'categorizations',
                    'alias' => 'c3',
                    'type' => 'inner',
                    'conditions' => array(
                        'c3.id = UserLine.level_excel',
                    ),
                ),
            ),
            'conditions' => array(
                'UserArea.status' => self::STATUS_ENABLED,
                'UserArea.user_id' => $userId,
            ),
            'order' => array(
                'u.nomina ASC',
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    /**
     * Buscamos los asociados con sus evaluaciones, validaciones y certificaciones
     * @param $userId
     * @return array
     */
    public function getAsociatesWithDetails($userId)
    {
        $filters = array(
            'fields' => array(
                'u.nomina',
                'l.name as line',
                'wc.name as workstation_config_name',
                'SUM(IF(cd.id IS NULL, 0, IF(cd.qualification>=wc.minimum_qualification, 1, 0))) as total'
            ),
            'joins' => array(
                array(
                    'table' => 'areas',
                    'alias' => '_a',
                    'type' => 'inner',
                    'conditions' => array(
                        '_a.status' => Area::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => '_l',
                    'type' => 'inner',
                    'conditions' => array(
                        '_l.status' => Line::STATUS_ENABLED,
                        '_l.area_id = _a.id',
                        '_l.type' => Line::TYPE_VIRTUAL,
                    ),
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'UserLine',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserLine.status' => UserLine::STATUS_ENABLED,
                        'UserLine.line_id = _l.id',
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'inner',
                    'conditions' => array(
                        'u.id = UserLine.user_id',
                        'u.status' => array(User::STATUS_ENABLED, User::STATUS_INCAPACIDAD, User::STATUS_VACACIONES),
                        'u.category_id' => array(Category::ASOCIADO),
                    ),
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'UserLine.line_id = l.id',
                        'l.status' => Line::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.area_id = a.id',
                        'a.status' => Area::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.moe_id = m.id',
                        'm.status' => Moe::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'certifications',
                    'alias' => 'cert',
                    'type' => 'left',
                    'conditions' => array(
                        'cert.user_id = UserLine.user_id',
                        'cert.status' => Certification::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd',
                    'type' => 'left',
                    'conditions' => array(
                        'cd.certification_id = cert.id',
                        'cd.status' => CertificationDetail::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc',
                    'type' => 'left',
                    'conditions' => array(
                        'wc.id = cd.workstation_config_id',
                        'wc.status' => WorkstationConfig::STATUS_ENABLED,
                    ),
                ),

            ),
            'conditions' => array(
                'UserArea.status' => self::STATUS_ENABLED,
                'UserArea.user_id' => $userId,
            ),
            'group' => array(
                'u.nomina',
                'l.name',
                'wc.name',
            ),
            'order' => array(
                'u.nomina ASC',
                'wc.orden ASC',
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }


}
